/**
 * 
 */
package de.damontools.awolowa.db;

import java.util.ArrayList;
import java.util.List;

import de.damontools.awolowa.application.Profile;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author damon
 * 
 */
public class DatabaseManager extends SQLiteOpenHelper {

	/*
	 * Database information
	 */
//	public interface DBInfo {
 
		// Database information
		public static final String DATABASE_NAME = "awolowa";
		public static final int DATABASE_VERSION = 1;
 
		// Database tables
		public static final String TABLE_NAME_PROFILES = "profiles";
		public static final String TABLE_NAME_OPTIONS = "options";
		public static final String TABLE_NAME_LOGS = "logs";
		
		// Table IDs
		public static final String OPTIONS_ID = "OptionsID";
		public static final String LOGS_ID = "LogsID";
		
		// Profiles Table Columns
		public static final String PROFILES_ID = "ProfilesID";
		public static final String PROFILES_COLUMN_PROFILENAME = "ProfilName";
		public static final String PROFILES_COLUMN_SSID = "SSID";
		public static final String PROFILES_COLUMN_MAC = "MAC";
		public static final String PROFILES_COLUMN_PACKAGESTOSEND = "PackagesToSend";
		public static final String PROFILES_COLUMN_GETGLOBALOPTION = "GetGlobalOption";
		public static final String PROFILES_COLUMN_ACTIVE = "Active";

		// Options Table Columns
//	}
	
	/*
	 * Constructor
	 */
	public DatabaseManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}


	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE "+TABLE_NAME_PROFILES+" ( "+ 
				PROFILES_ID +" INTEGER PRIMARY KEY AUTOINCREMENT, " +
				PROFILES_COLUMN_PROFILENAME+" TEXT not null, " +
				PROFILES_COLUMN_SSID+" TEXT, " +
				PROFILES_COLUMN_MAC+" TEXT, " +
				PROFILES_COLUMN_PACKAGESTOSEND+" INTEGER, " +
				PROFILES_COLUMN_GETGLOBALOPTION+" BOOLEAN DEFAULT TRUE, " +
				PROFILES_COLUMN_ACTIVE+" BOOLEAN DEFAULT TRUE)");
		
		db.execSQL("CREATE TABLE "+TABLE_NAME_OPTIONS+"(" +
				""+OPTIONS_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
				"StartOnBoot BOOLEAN, " +
				"PackagesToSend INTEGER DEFAULT 3, " +
				"ShowToast BOOLEAN, " +
				"Vibration BOOLEAN, "+
				"VerboseLog BOOLEAN);");
		
		db.execSQL("CREATE TABLE "+TABLE_NAME_LOGS+"(" +
				LOGS_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
				"Time TIMESTAMP," +
				"ProfileID INTEGER);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS table " +TABLE_NAME_PROFILES);
		db.execSQL("DROP TABLE IF EXISTS table " +TABLE_NAME_OPTIONS);
		db.execSQL("DROP TABLE IF EXISTS table " +TABLE_NAME_LOGS);
		onCreate(db);
	}
	/*
	 * Add a Profile to DB
	 */
	public void addProfile(Profile profile){
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(PROFILES_COLUMN_PROFILENAME, profile.getProfilName());
		values.put(PROFILES_COLUMN_SSID, profile.getSSID());
		values.put(PROFILES_COLUMN_SSID, "Testname");
		values.put(PROFILES_COLUMN_MAC, profile.getMAC());
		values.put(PROFILES_COLUMN_PACKAGESTOSEND, profile.getNrPackages());
		values.put(PROFILES_COLUMN_GETGLOBALOPTION, profile.isGetGlobalOption());
		values.put(PROFILES_COLUMN_ACTIVE, profile.isActive());
		
		db.insert(TABLE_NAME_PROFILES, null, values);
		db.close();
	}
	/*
	 * Get a Profile from DB
	 */
	public Profile getProfile(int id){
		SQLiteDatabase db = this.getReadableDatabase();
		String[] columns = { PROFILES_ID,
				PROFILES_COLUMN_PROFILENAME,
				PROFILES_COLUMN_SSID,
				PROFILES_COLUMN_MAC,
				PROFILES_COLUMN_PACKAGESTOSEND,
				PROFILES_COLUMN_GETGLOBALOPTION,
				PROFILES_COLUMN_ACTIVE};
		
		Cursor cursor = db.query(TABLE_NAME_PROFILES, columns, PROFILES_ID + "=?", new String[]{String.valueOf(id)},null,null,null,null );
		if(cursor != null)
			cursor.moveToFirst();
		Profile profile = new Profile(Integer.parseInt(cursor.getString(0)), cursor.getString(1),cursor.getString(2),cursor.getString(3),Integer.parseInt(cursor.getString(4)),cursor.getString(5),Boolean.parseBoolean(cursor.getString(6)));
		return profile;
	}
	/*
	 * Get the list of all Profiles
	 */
	public List<Profile> getAllProfiles(){
		List<Profile> profileList = new ArrayList<Profile>();
		String selectQuery = "SELECT * FROM " + TABLE_NAME_PROFILES;
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if(cursor.moveToFirst()){
			do{
				Profile profile = new Profile();
				profile.setID(Integer.parseInt(cursor.getString(0)));
				profile.setProfilName(cursor.getString(1));
				profile.setSSID(cursor.getString(2));
				profile.setMAC(cursor.getString(3));
				profile.setNrPackages(Integer.parseInt(cursor.getString(4)));
				profile.setGetGlobalOption(cursor.getString(5));
				profile.setActive(Boolean.parseBoolean(cursor.getString(6)));
				profileList.add(profile);
			} while(cursor.moveToNext());
		}
//		cursor.close();
		return profileList;
	}
	
	public int getProfilesCount(){
		String countQuery = "SELECT * FROM " + TABLE_NAME_PROFILES;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();
		return cursor.getCount();
	}
	/*
	 * Update Profile Data
	 */
	public int updateProfile(Profile profile){
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(PROFILES_COLUMN_PROFILENAME, profile.getProfilName());
		values.put(PROFILES_COLUMN_SSID, profile.getSSID());
		values.put(PROFILES_COLUMN_MAC, profile.getMAC());
		values.put(PROFILES_COLUMN_PACKAGESTOSEND, profile.getNrPackages());
		values.put(PROFILES_COLUMN_GETGLOBALOPTION, profile.isGetGlobalOption());
		values.put(PROFILES_COLUMN_ACTIVE, profile.isActive());
		
		return db.update(TABLE_NAME_PROFILES, values, PROFILES_ID + " = ?", new String[]{String.valueOf(profile.getID())});
	}
	/*
	 * Delete a Profile
	 */
	public void deleteProfile(Profile profile){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME_PROFILES, PROFILES_ID + " = ?", new String[] {String.valueOf(profile.getID())});
		db.close();
	}
	/*
	 * Open database for write operations
	 */
	public SQLiteDatabase openWritable() { 
		return getWritableDatabase();
	}
 
	/*
	 * Open database for read operations
	 */
	public SQLiteDatabase openReadable() {
		return getReadableDatabase();
	}
}